﻿#region Librerias
using System;
using System.Collections.Generic;
using System.Web;
using System.Data;
#endregion

/// <summary>
/// Class QuestionAnswer, dependiente de la clase Person, define una persona natural
/// </summary>
//tratamiento especial para AGE
#region Class

public class clsQuestionAnswer : DAC {
    #region Variables

    private int idQuestionAnswer, idClient, idUser, iFlag;
    private String sTopic, sQuestion, sAnswer, sNameClient, sNameAdministrator;
    private const String tabla = "dbo.tblQuestionAnswer";

    #endregion

    #region Builder

    /// <summary>
    /// Constructor
    /// </summary>
    public clsQuestionAnswer() { }

    #endregion

    #region Methods

    #region Consultations

    public DataSet SeeAllTheRecords() {
        String query = "SELECT row_number() over(order by " + tabla + ".idQuestionAnswer) As NUMBER, ";
        query += tabla + ".idQuestionAnswer As ID, " + tabla + ".idClient As CLIENT, ";
        query += tabla + ".idUser As USERR, ";
        query += tabla + ".sNameClient As CLIENT, " + tabla + ".sNameAdministrator As ADMINISTRATOR, ";
        query += tabla + ".sTopic As TOPIC, ";
        query += tabla + ".sQuestion As QUESTION, " + tabla + ".sAnswer As ANSWER, ";
        query += "(CASE WHEN " +  tabla + ".iFlag = 1 THEN 'No leido' ELSE 'Leido' END) As FLAG ";
        query += "FROM " + tabla + " ";
        query += "ORDER BY " + tabla + ".idQuestionAnswer DESC";
        return this.ExecuteQuery(query);
    }

    public DataSet SeeAllTheRecords(int startRecord, int maxRecord, String text){
        String query = "SELECT row_number() over(order by " + tabla + ".idQuestionAnswer) As NUMBER, ";
        query += tabla + ".idQuestionAnswer As ID, " + tabla + ".idClient As CLIENT, ";
        query += tabla + ".idUser As USERR, ";
        query += tabla + ".sNameClient As CLIENT, " + tabla + ".sNameAdministrator As ADMINISTRATOR, ";
        query += tabla + ".sTopic As TOPIC, ";
        query += tabla + ".sQuestion As QUESTION, " + tabla + ".sAnswer As ANSWER, ";
        query += "(CASE WHEN " +  tabla + ".iFlag = 1 THEN 'No leido' ELSE 'Leido' END)  As FLAG ";
        query += "FROM " + tabla + " ";
        if(!text.Equals("")){
            query += " WHERE " + tabla + ".sTopic LIKE '%" + text + "%' ";
            query += " OR " + tabla + ".sQuestion LIKE '%" + text + "%' ";
            query += " OR " + tabla + ".sAnswer LIKE '%" + text + "%' ";
        }
        query += "ORDER BY " + tabla + ".idQuestionAnswer DESC";
        if (startRecord == 0 && maxRecord == 0) {
            return this.ExecuteQuery(query);
        }
        else {
            return this.ExecuteQuery(query, startRecord, maxRecord);
        }        
    }

    public DataSet SeeAllTheRecords(int startRecord, int maxRecord, String text, string idClient){
        String query = "SELECT row_number() over(order by " + tabla + ".idQuestionAnswer) As NUMBER, ";
        query += tabla + ".idQuestionAnswer As ID, " + tabla + ".idClient As CLIENT, ";
        query += tabla + ".idUser As USERR, ";
        query += tabla + ".sNameClient As CLIENT, " + tabla + ".sNameAdministrator As ADMINISTRATOR, ";
        query += tabla + ".sTopic As TOPIC, ";
        query += tabla + ".sQuestion As QUESTION, " + tabla + ".sAnswer As ANSWER, ";
        query += "(CASE WHEN " +  tabla + ".iFlag = 1 THEN 'No leido' ELSE 'Leido' END)  As FLAG ";
        query += "FROM " + tabla + " ";
        query += "WHERE " + tabla + ".idClient=" + idClient + " ";
        if(!text.Equals("")){
            query += "AND (" + tabla + ".sTopic LIKE '%" + text + "%' ";
            query += " OR " + tabla + ".sQuestion LIKE '%" + text + "%' ";
            query += " OR " + tabla + ".sAnswer LIKE '%" + text + "%') ";
        }
        query += "ORDER BY " + tabla + ".idQuestionAnswer DESC";
        if (startRecord == 0 && maxRecord == 0) {
            return this.ExecuteQuery(query);
        }
        else {
            return this.ExecuteQuery(query, startRecord, maxRecord);
        }        
    }

    public DataSet ConsultID(String id) {
        String query = "SELECT ";
        query += tabla + ".idQuestionAnswer As ID, " + tabla + ".idClient As CLIENT, ";
        query += tabla + ".idUser As USERR, ";
        query += tabla + ".sNameClient As CLIENT, " + tabla + ".sNameAdministrator As ADMINISTRATOR, ";
        query += tabla + ".sTopic As TOPIC, ";
        query += tabla + ".sQuestion As QUESTION, " + tabla + ".sAnswer As ANSWER, ";
        query += tabla + ".iFlag As FLAG ";
        query += "FROM " + tabla;
        query += " WHERE " + tabla + ".idQuestionAnswer =" + id + " ";
        query += "ORDER BY " + tabla + ".idQuestionAnswer DESC";
        return this.ExecuteQuery(query);
    }

    public DataSet ConsultClientID(String id) {
        String query = "SELECT ";
        query += tabla + ".idQuestionAnswer As ID, " + tabla + ".idClient As CLIENT, ";
        query += tabla + ".idUser As USERR, ";
        query += tabla + ".sNameClient As CLIENT, " + tabla + ".sNameAdministrator As ADMINISTRATOR, ";
        query += tabla + ".sTopic As TOPIC, ";
        query += tabla + ".sQuestion As QUESTION, " + tabla + ".sAnswer As ANSWER, ";
        query += tabla + ".iFlag As FLAG ";
        query += "FROM " + tabla;
        query += " WHERE " + tabla + ".idClient =" + id;
        return this.ExecuteQuery(query);
    }

    #endregion

    public int Insert() {
        String query = "INSERT INTO " + tabla;
        query += "(idClient, idUser, sNameClient, sNameAdministrator, sTopic, sQuestion, sAnswer, iFlag) ";
        query += "VALUES ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}') SELECT CAST(scope_identity() AS int)";

        String sql = String.Format(query, this.IdClient, this.IdUser, this.SNameClient, this.SNameAdministrator, this.STopic, this.SQuestion, this.SAnswer, this.IFlag);
        return this.ExecuteDML(sql, "no");
    }

    public int Update(){
        String query = "UPDATE " + tabla + " SET ";        
        query += tabla + ".idClient=" + this.IdClient + ", " + tabla + ".idUser=" + this.IdUser + ", ";
        query += tabla + ".sNameClient='" + this.SNameClient + "', " + tabla + ".sNameAdministrator='" + this.SNameAdministrator + "', ";
        query += tabla + ".sTopic='" + this.STopic + "', " + tabla + ".sQuestion='" + this.SQuestion + "', ";
        query += tabla + ".sAnswer='" + this.SAnswer + "', " + tabla + ".iFlag=" + this.IFlag + " ";
        query += "WHERE " + tabla + ".idQuestionAnswer=" + this.ID;
        return this.ExecuteDML(query, "no");
    }

    public int Delete()
    {
        String query = "DELETE FROM " + tabla + " WHERE " + tabla + ".idQuestionAnswer={0}";
        String sql = String.Format(query, this.ID);
        return this.ExecuteDML(sql, "no");
    }

    #endregion

    #region Setters and Getters

    
    public int ID
    {
        get { return idQuestionAnswer; }
        set { idQuestionAnswer = value; }
    }

    public int IdClient {
        get { return idClient; }
        set { idClient = value; }
    }
    
    public String STopic
    {
        get { return sTopic; }
        set { sTopic = value; }
    }
    
    public String SAnswer {
        get { return sAnswer; }
        set { sAnswer = value; }
    }

    public String SQuestion {
        get { return sQuestion; }
        set { sQuestion = value; }
    }
    public int IFlag {
        get { return iFlag; }
        set { iFlag = value; }
    }

    public int IdUser {
        get { return idUser; }
        set { idUser = value; }
    }

    public String SNameAdministrator {
        get { return sNameAdministrator; }
        set { sNameAdministrator = value; }
    }

    public String SNameClient {
        get { return sNameClient; }
        set { sNameClient = value; }
    }
    #endregion

}

#endregion